package com.meiyuetao.myt.chart.web.action;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lab.s2jh.core.util.DateUtils;
import lab.s2jh.core.web.SimpleController;

import org.apache.struts2.rest.HttpHeaders;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.google.common.collect.Lists;

public class BoxOrderChartController extends SimpleController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public HttpHeaders count() {
        return buildDefaultHttpHeaders("count");
    }

    public HttpHeaders quantityDatas() {
        String dateFrom = this.getRequiredParameter("dateFrom");
        String dateTo = DateUtils.plusOneDay(this.getRequiredParameter("dateTo"));
        String groupBy = this.getRequiredParameter("groupBy");
        final DateFormat df = groupBy.equals("year") ? DateUtils.FORMAT_YYYY_FORMATER : (groupBy.equals("month") ? DateUtils.FORMAT_YYYYMM_FORMATER
                : DateUtils.FORMAT_YYYYMMDD_FORMATER);
        StringBuilder sql = new StringBuilder();
        sql.append("select cdate,count(*) as cnt from ");
        String len = groupBy.equals("year") ? "4" : (groupBy.equals("month") ? "6" : "8");
        sql.append("(select sid, convert(nvarchar(" + len + "),created_dt,112) as cdate from iyb_box_order ");
        sql.append("where order_status!='S10O' and order_status!='S90CANCLE' ");
        sql.append("and created_dt >=:dateFrom and created_dt < :dateTo and order_from in (:orderFrom)) t ");
        sql.append("GROUP BY t.cdate ");
        sql.append("order by t.cdate desc ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        args.addValue("dateFrom", dateFrom);
        args.addValue("dateTo", dateTo);
        args.addValue("orderFrom", Lists.newArrayList(getRequest().getParameterValues("orderFrom")));

        List<Object[]> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String cdate = rs.getString("cdate");
                int cnt = rs.getInt("cnt");
                Object[] data = { DateUtils.parseDate(cdate, df).getTime(), cnt };
                return data;
            }
        });
        setModel(datas);
        return buildDefaultHttpHeaders();
    }

    public HttpHeaders amountDatas() {
        String dateFrom = this.getRequiredParameter("dateFrom");
        String dateTo = DateUtils.plusOneDay(this.getRequiredParameter("dateTo"));
        String groupBy = this.getRequiredParameter("groupBy");
        final DateFormat df = groupBy.equals("year") ? DateUtils.FORMAT_YYYY_FORMATER : (groupBy.equals("month") ? DateUtils.FORMAT_YYYYMM_FORMATER
                : DateUtils.FORMAT_YYYYMMDD_FORMATER);
        StringBuilder sql = new StringBuilder();
        sql.append("select cdate,sum(actual_amount) as actual_amount from ");
        String len = groupBy.equals("year") ? "4" : (groupBy.equals("month") ? "6" : "8");
        sql.append("(select sid, convert(nvarchar(" + len + "),created_dt,112) as cdate,actual_amount from iyb_box_order ");
        sql.append("where order_status!='S10O' and order_status!='S90CANCLE' ");
        sql.append("and created_dt >=:dateFrom and created_dt < :dateTo and order_from in (:orderFrom)) t ");
        sql.append("GROUP BY t.cdate ");
        sql.append("order by t.cdate desc ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        args.addValue("dateFrom", dateFrom);
        args.addValue("dateTo", dateTo);
        args.addValue("orderFrom", Lists.newArrayList(getRequest().getParameterValues("orderFrom")));

        List<Object[]> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String cdate = rs.getString("cdate");
                double cnt = rs.getDouble("actual_amount");
                Object[] data = { DateUtils.parseDate(cdate, df).getTime(), cnt };
                return data;
            }
        });
        setModel(datas);
        return buildDefaultHttpHeaders();
    }

    public HttpHeaders commodityDetailDatas() {
        String dateFrom = this.getRequiredParameter("dateFrom");
        String dateTo = DateUtils.plusOneDay(this.getRequiredParameter("dateTo"));

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT c.barcode,c.title,tab1.quantity,tab1.order_num FROM( ");
        sql.append(" SELECT row_number () OVER (ORDER BY tab0.quantity) AS order_num,tab0.quantity,tab0.commodity_sid FROM( ");

        sql.append(" SELECT bodc.commodity_sid,SUM (bodc.quantity) AS quantity FROM ");
        sql.append("iyb_box_order bo,iyb_box_order_detail bod,iyb_box_order_detail_commodity bodc ");
        sql.append("where bo.order_from in (:orderFrom) and bod.pay_time>=:dateFrom and bod.pay_time<=:dateTo ");
        sql.append("and bo.sid=bodc.order_sid and bodc.order_detail_sid = bod.sid and bodc.is_gift<>1 ");
        sql.append("group by bodc.commodity_sid) tab0 ) tab1, iyb_commodity c where tab1.commodity_sid=c.sid  ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        args.addValue("dateFrom", dateFrom);
        args.addValue("dateTo", dateTo);
        args.addValue("orderFrom", Lists.newArrayList(getRequest().getParameterValues("orderFrom")));

        List<Object[]> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                String barcode = rs.getString("barcode");
                int quantity = rs.getInt("quantity");
                Object[] data = { quantity, orderNum - 1 };
                return data;
            }
        });
        List<Object[]> ticks = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                String barcode = rs.getString("barcode");
                int quantity = rs.getInt("quantity");
                Object[] data = { orderNum - 1, title + "  (" + quantity + ")" };
                return data;
            }
        });
        Map<String, List<Object[]>> map = new HashMap<String, List<Object[]>>();
        map.put("datas", datas);
        map.put("ticks", ticks);
        setModel(map);
        return buildDefaultHttpHeaders();
    }

    public HttpHeaders commodityOrderDatas() {
        String dateFrom = this.getRequiredParameter("dateFrom");
        String dateTo = DateUtils.plusOneDay(this.getRequiredParameter("dateTo"));

        StringBuilder sql = new StringBuilder();
        sql.append("select c.barcode,c.title,tab1.quantity ,tab1.order_num from ( ");
        sql.append(" select row_number()over(order by tab0.quantity) as order_num,tab0.quantity,tab0.commodity_sid from( ");

        sql.append("select bodc.commodity_sid,sum(bodc.quantity)as quantity from ");
        sql.append("iyb_box_order bo,iyb_box_order_detail_commodity bodc ");
        sql.append("where order_from in (:orderFrom) and bo.order_time>=:dateFrom and bo.order_time<:dateTo ");
        sql.append("and bo.sid=bodc.order_sid and bodc.is_gift<>1 ");
        sql.append("group by bodc.commodity_sid) tab0 ) tab1, iyb_commodity c where tab1.commodity_sid=c.sid  ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        args.addValue("dateFrom", dateFrom);
        args.addValue("dateTo", dateTo);
        args.addValue("orderFrom", Lists.newArrayList(getRequest().getParameterValues("orderFrom")));

        List<Object[]> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                String barcode = rs.getString("barcode");
                int quantity = rs.getInt("quantity");
                Object[] data = { quantity, orderNum - 1 };
                return data;
            }
        });
        List<Object[]> ticks = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                String barcode = rs.getString("barcode");
                int quantity = rs.getInt("quantity");
                Object[] data = { orderNum - 1, title + "  (" + quantity + ")" };
                return data;
            }
        });
        Map<String, List<Object[]>> map = new HashMap<String, List<Object[]>>();
        map.put("datas", datas);
        map.put("ticks", ticks);
        setModel(map);
        return buildDefaultHttpHeaders();
    }

    public HttpHeaders commodityViewDatas() {
        String dateFrom = this.getRequiredParameter("dateFrom");
        String dateTo = DateUtils.plusOneDay(this.getRequiredParameter("dateTo"));

        StringBuilder sql = new StringBuilder();
        sql.append("select c.barcode,c.title,tab1.quantity ,tab1.order_num from ( ");
        sql.append(" select row_number()over(order by tab0.quantity) as order_num,tab0.quantity,tab0.commodity_sid from( ");

        sql.append("select bodc.commodity_sid,sum(bodc.quantity)as quantity from ");
        sql.append("iyb_box_order bo,iyb_box_order_detail_commodity bodc ");
        sql.append("where order_from in (:orderFrom) and bo.order_time>=:dateFrom and bo.order_time<:dateTo ");
        sql.append("and bo.sid=bodc.order_sid and bodc.is_gift<>1 ");
        sql.append("group by bodc.commodity_sid) tab0 ) tab1, iyb_commodity c where tab1.commodity_sid=c.sid  ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        args.addValue("dateFrom", dateFrom);
        args.addValue("dateTo", dateTo);
        args.addValue("orderFrom", Lists.newArrayList(getRequest().getParameterValues("orderFrom")));

        List<Object[]> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                String barcode = rs.getString("barcode");
                int quantity = rs.getInt("quantity");
                Object[] data = { quantity, orderNum - 1 };
                return data;
            }
        });
        List<Object[]> ticks = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<Object[]>() {
            public Object[] mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                String title = rs.getString("title");
                int orderNum = rs.getInt("order_num");
                String barcode = rs.getString("barcode");
                int quantity = rs.getInt("quantity");
                Object[] data = { orderNum - 1, title + "  (" + quantity + ")" };
                return data;
            }
        });
        Map<String, List<Object[]>> map = new HashMap<String, List<Object[]>>();
        map.put("datas", datas);
        map.put("ticks", ticks);
        setModel(map);
        return buildDefaultHttpHeaders();
    }
}
